# -*- coding: UTF-8 -*- #
import pandas as pd
import numpy as npy

user_id = ['1001','1002','1003','1004','1005','1006']

user_id_score = {'1001': 0,
                 '1002': 0,
                 '1003': 0,
                 '1004': 0,
                 '1005': 0,
                 '1006': 0}
nums = {'1001': 0,
        '1002': 0,
        '1003': 0,
        '1004': 0,
        '1005': 0,
        '1006': 0}

data = pd.read_excel(u'test.xlsx')
data = data.values
# print(npy.isnan(data[109][4]))
# print(data[0])
for i in range(0, len(data)):
    if not npy.isnan(data[i][4]):
        if data[i][4] > 5000:
            data[i][4] = 5000
        elif data[i][4] < 100:
            data[i][4] = 100
        user_id_score[str(data[i][0])] = int(user_id_score[str(data[i][0])]) + int(data[i][4])
        nums[str(data[i][0])] = nums[str(data[i][0])] + 1
    else:
        nums[str(data[i][0])] = nums[str(data[i][0])] - 1

user_score = user_id_score

for i in range(0, len(user_id_score)):
    user_id_score[user_id[i]] = user_id_score[user_id[i]] / nums[user_id[i]]

for i in range(0, len(data)):
    if not npy.isnan(data[i][4]):
        pass
    else:
        data[i][4] = str(user_id_score[str(data[i][0])])
# 第一小问
print("第一小问：经过数据清洗后的数据为：")
print(data)

# 第二小问，按照员工号排序

user_score=sorted(user_score.items(),key=lambda x:x[1],reverse=False)
print("第二小问，按照员工号排序："+str(user_score))

